﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Products : System.Web.UI.UserControl
{
    public static string connString = "user id=portaluser;data source=172.25.10.8;initial catalog=VoiceNet_Focus;password=sqluser";

    public SqlConnection conn = new SqlConnection(connString);


    protected void Page_Load(object sender, System.EventArgs e)
    {
        //Page.ClientScript.RegisterClientScriptInclude(this.GetType(), "confirmdeletekey", ResolveClientUrl("~/scripts/ConfirmDelete.js"));

        if (!IsPostBack)
        {
            //btnClear.Attributes.Add("confirmdeletekey", "return confirm('Are you sure you want to delete all products for this order?');");
            //btnClear.Attributes["confirmdeletekey"];

            //Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "confirmdeletekey", "function confirm_delete() { return confirm('Are you sure you want to delete the all products for this order?'); }", true);
            //Page.ClientScript.RegisterClientScriptInclude("confirmdeletekey", "ConfirmDelete.js");



            //btnClear.Attributes["confirmdeletekey"]

            loadProductTypes();

            loadOrders();
        }


    }

    private void loadProductTypes()
    {
        SqlDataAdapter ProductTypesDA = default(SqlDataAdapter);
        DataSet ProductTypesDS = default(DataSet);

        connString = "user id=portalUser;data source=" + Session["Servername"] + ";initial catalog=VoiceNet_Focus;password=sqluser";
        conn.ConnectionString = connString;

        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
        string lccommand = null;

        lccommand = "Select distinct wpt.producttypeid, wpt.producttype from webproducts wp (nolock) left outer join webproducttypes wpt (nolock) on wp.producttypeid = wpt.producttypeid where wp.active = 1 and wpt.active = 1  and wp.campaignname = '" + Session["campaignname"] + "' order by wpt.producttype ";

        ProductTypesDA = new SqlDataAdapter(lccommand, conn);
        ProductTypesDS = new DataSet();
        ProductTypesDA.Fill(ProductTypesDS, "AllTables");
        //ddlProductTypes.DataSource = ProductTypesDS;
        ddlProductTypes.DataSource = ProductTypesDS.Tables[0];
        ddlProductTypes.DataTextField = ProductTypesDS.Tables[0].Columns["producttype"].ColumnName.ToString();
        ddlProductTypes.DataValueField = ProductTypesDS.Tables[0].Columns["producttypeid"].ColumnName.ToString();
        ddlProductTypes.DataBind();
        ddlProductTypes.Items.Insert(0, new ListItem("", ""));
        //ddlProductTypes.SelectedIndex = 0;
        lblError1.Text = "";
    }

    private void loadProducts()
    {
        SqlDataAdapter ProductsDA = default(SqlDataAdapter);
        DataSet ProductsDS = default(DataSet);

        connString = "user id=portalUser;data source=" + Session["Servername"] + ";initial catalog=VoiceNet_Focus;password=sqluser";
        conn.ConnectionString = connString;

        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
        string lccommand = null;

        lccommand = "Select * from webproducts with (NOLOCK) where producttypeid = " + ddlProductTypes.SelectedValue + " and Active = 1 order by productdesc";


        ProductsDA = new SqlDataAdapter(lccommand, conn);
        ProductsDS = new DataSet();

        ProductsDA.Fill(ProductsDS, "AllTables");
        lbProducts.DataSource = ProductsDS.Tables[0];
        lbProducts.DataTextField = ProductsDS.Tables[0].Columns["productdesc"].ColumnName.ToString();
        lbProducts.DataValueField = ProductsDS.Tables[0].Columns["productid"].ColumnName.ToString();
        lbProducts.DataBind();
    }

    protected void ddlProductTypes_SelectedIndexChanged(object sender, System.EventArgs e)
    {
        if (ddlProductTypes.SelectedIndex > 0)
        {
            loadProducts();
        }
    }

    protected void btnAdd_Click(object sender, System.EventArgs e)
    {
        if (lbProducts.SelectedIndex != -1)
        {
            lblError1.Text = "";
            lblError2.Text = "";
            WriteProduct();
            ddlProductQty.SelectedValue = "1";
            loadOrders();

        }

    }

    protected void btnRemove_Click(object sender, System.EventArgs e)
    {
        if (lbOrderProducts.SelectedIndex != -1)
        {
            //remove item from orders table
            string lccommand = null;
            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

            connString = "user id=portaluser;data source=" + Session["servername"] + ";initial catalog=" + Session["projectname"] + ";password=sqluser";
            conn.ConnectionString = connString;
            conn.Open();
            lccommand = "Delete from orders where orderid = '" + lbOrderProducts.SelectedValue + "'";

            command.CommandType = CommandType.Text;
            command.CommandText = lccommand;
            command.Connection = conn;

            try
            {
                if (command.ExecuteNonQuery() == 1)
                {
                    lbOrderProducts.Items.RemoveAt(lbOrderProducts.SelectedIndex);
                    lblError2.Text = "";
                }
                else
                {
                    lblError2.ForeColor = System.Drawing.Color.Red;
                    lblError2.Text = "Failed to Remove selected Order. 1";
                    conn.Close();

                }
            }
            catch
            {
                lblError2.ForeColor = System.Drawing.Color.Red;
                lblError2.Text = "Failed to Remove selected Order. 2";
                //& connString & ": " & lccommand
                conn.Close();

            }
            conn.Close();

        }
    }

    protected void btnClear_Click(object sender, System.EventArgs e)
    {
        //        Page.ClientScript.RegisterClientScriptInclude("confirmdeletekey", ResolveClientUrl("~/scripts/ConfirmDelete.js"));

        string lccommand = null;
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

        connString = "user id=portaluser;data source=" + Session["servername"] + ";initial catalog=" + Session["projectname"] + ";password=sqluser";
        conn.ConnectionString = connString;
        conn.Open();
        lccommand = "Delete from orders where enterpriselistid = '" + Session["listid"] + "'";

        command.CommandType = CommandType.Text;
        command.CommandText = lccommand;
        command.Connection = conn;

        try
        {
            if (command.ExecuteNonQuery() >= 0)
            {
                lbOrderProducts.Items.Clear();
                lblError2.Text = "";
            }
            else
            {
                lblError2.ForeColor = System.Drawing.Color.Red;
                lblError2.Text = "Failed to Remove all Orders 1.";

            }
        }
        catch
        {
            lblError2.ForeColor = System.Drawing.Color.Red;
            lblError2.Text = "Failed to remove all orders 2. " + lccommand;

        }
        conn.Close();

        //remove all items from orders table with this listid
    }

    public void WriteProduct()
    {
        string lccommand = null;
        SqlCommand command = new SqlCommand();

        string productqty = "";
        string productid = "";
        string productdesc = "";
        string ProdtypeID = "";
        string ProdRev = "";

        //get product data from product table
        connString = "user id=portalUser;data source=" + Session["servername"] + ";initial catalog=VoiceNet_Focus;password=sqluser";
        conn.ConnectionString = connString;
        conn.Open();

        lccommand = "Select * from webproducttypes pt (nolock) left join webproducts p on pt.producttypeid = p.producttypeid where p.productid = " + lbProducts.SelectedValue;
        command.CommandType = CommandType.Text;
        command.CommandText = lccommand;
        command.Connection = conn;

        System.Data.SqlClient.SqlDataReader dr = default(System.Data.SqlClient.SqlDataReader);
        dr = command.ExecuteReader(CommandBehavior.SingleRow);
        if (dr.HasRows)
        {
            dr.Read();

            productqty = ddlProductQty.SelectedValue.ToString();
            productid = lbProducts.SelectedValue.ToString();
            productdesc = dr["productdesc"].ToString();
            ProdtypeID = dr["producttypeid"].ToString();
            ProdRev = dr["baserevenue"].ToString();
            dr.Close();
            dr = null;
            conn.Close();


        }
        else
        {
            lblError1.Text = "Failed to get product info from WebProducts table";
            dr.Close();
            dr = null;
            conn.Close();
        }


        ////insert into orders table

        bool prodFound = false;


        connString = "user id=portaluser;data source=" + Session["servername"] + ";initial catalog=" + Session["projectname"] + ";password=sqluser";
        conn.ConnectionString = connString;
        conn.Open();

        lbOrderProducts.ClearSelection();
        lccommand = "select * from orders where enterpriselistid = '" + Session["listid"] + "' and productid = " + (string)lbProducts.SelectedValue;

        command.CommandType = CommandType.Text;
        command.CommandText = lccommand;
        command.Connection = conn;


        dr = command.ExecuteReader(CommandBehavior.SingleRow);
        if (dr.HasRows)
        {
            //dr.Read();
            prodFound = true;
        }
        else
        {
            prodFound = false;
        }

        dr = null;
        conn.Close();


        if (prodFound)  //if product already exists for this order, add quantity
        {
            conn.Open();
            lccommand = "update orders set quantity = quantity + " + ddlProductQty.SelectedValue + " where enterpriselistid = '" + Session["listid"] + "' and productid = " + (string)lbProducts.SelectedValue;
            command.CommandType = CommandType.Text;
            command.CommandText = lccommand;
            command.Connection = conn;

            if (command.ExecuteNonQuery() == 1)
            {
                //it worked
            }
            else
            {
                //it didn't work
            }
            conn.Close();

        }
        else
        {
            conn.Open();

            lccommand = "insert into orders (enterpriseListid, ProductID, ProductTypeID, ProductDesc, BaseRevenue, Quantity) " +
                    " values (" + Session["listid"] + "," + productid + "," + ProdtypeID + ", '" + productdesc + "', " + ProdRev + ", " + productqty + ")";

            command.CommandType = CommandType.Text;
            command.CommandText = lccommand;
            command.Connection = conn;

            if (command.ExecuteNonQuery() >= 0)
            {
                //it worked
            }
            else
            {
                //it didn't work
            }
            conn.Close();
        }

    }

    private void loadOrders()
    {

        SqlDataAdapter OrdersDA = default(SqlDataAdapter);
        DataSet OrdersDS = default(DataSet);
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
        string lccommand = null;

        connString = "user id=portaluser;data source=" + Session["servername"] + ";initial catalog=" + Session["projectname"] + ";password=sqluser";
        conn.ConnectionString = connString;

        lccommand = "Select right('0' + convert(varchar, quantity),2) + ' - ' + productdesc as lbText, * from Orders with (NOLOCK) where enterpriselistid = '" + Session["listid"] + "' order by productdesc";

        OrdersDA = new SqlDataAdapter(lccommand, conn);
        OrdersDS = new DataSet();
        OrdersDA.Fill(OrdersDS, "orders");
        if (OrdersDS.Tables[0].Rows.Count > 0)
        {
            lbOrderProducts.DataSource = OrdersDS;
            lbOrderProducts.DataSource = OrdersDS.Tables[0];
            lbOrderProducts.DataTextField = OrdersDS.Tables[0].Columns["lbText"].ColumnName.ToString();
            lbOrderProducts.DataValueField = OrdersDS.Tables[0].Columns["OrderID"].ColumnName.ToString();
            lbOrderProducts.DataBind();

        }
        else
        {
            //lblError2.Text = "No Rows selected from Orders table ";

        }


    }

}
